//
//  MovieListDB.m
//  UI-豆瓣项目APP
//
//  Created by liuxin on 15/11/3.
//  Copyright © 2015年 孙鹏. All rights reserved.
//

#import "MovieListDB.h"
#import <sqlite3.h>
@implementation MovieListDB
+(instancetype)shareDataBase{
    static MovieListDB *dataBase = nil;
    if(dataBase == nil){
        dataBase = [[MovieListDB alloc]init];
    }
    return dataBase;
}
static sqlite3 *db = nil;
//sqlite数据库是基于C语言的底层数据库

//打开数据库
-(void)openDB{
    if(db != nil){
        NSLog(@"数据库已经打开了");
        return ;
    }
    //    打开数据库
    //    参数1:数据库文件
    //    参数2:数据库对象
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"movieList.sqlite"];
    NSLog(@"%@",sqlPath);
    //    需要转成utf8的中文编码
    BOOL result =  sqlite3_open(sqlPath.UTF8String, &db);
    if(result == SQLITE_OK){
        NSLog(@"yes 打开成功");
    }else{
        NSLog(@"no 打开失败");
    }
}
//建表
-(void)createTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS movielist(number INTEGER PRIMARY KEY AUTOINCREMENT,movieId TEXT,movieName TEXT,pic_url TEXT)";
    BOOL result =  sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"创建表成功");
    }
}
//插入一个电影
-(void)insertStudent:(MovieList *)movie{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO movielist(movieId,movieName,pic_url)VALUES ('%@','%@','%@')",movie.movieId,movie.movieName,movie.pic_url];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"插入成功");
    }
}
//查询电影
-(NSMutableArray*)selectSutdent{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM movielist"];
    //    创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    //    查询前的准备工作
    //    参数3:限制sql语句长度,-1为不限制
    //    将数据库对象db,sql语句,跟随指针对象stmt关联一起
    BOOL result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"准备查询成功");
    }else{
        NSLog(@"查询失败");
    }
    //    循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        //        取出第一列
//        int number =  sqlite3_column_int(stmt, 0);
        //        取出第二列数据
        const unsigned char *movieid = sqlite3_column_text(stmt, 1);
        //        取出第三列数据
        const unsigned char *moviename = sqlite3_column_text(stmt, 2);
        //        取出第四列数据
        const unsigned char *pic = sqlite3_column_text(stmt, 3);
        //        将C语言类型转换为OC的
//        NSInteger num = number;
        NSString *movieID = [NSString stringWithUTF8String:(const char*)movieid];
        NSString* movieName = [NSString stringWithUTF8String:(const char*)moviename];
        NSString *picUrl = [NSString stringWithUTF8String:(const char*)pic];
        MovieList *movie = [MovieList new];
        movie.movieId = movieID;
        movie.movieName = movieName;
        movie.pic_url = picUrl;
        [array addObject:movie];
    }
    return array;
}
-(void)updateStudent:(MovieList*)movie name:(NSString*)name{
//    NSString *sql = [NSString stringWithFormat:@"UPDATE lanou0920 SET name = '%@', gender = '%@', age = '%ld' WHERE name = '%@'",stu.name,stu.gender,stu.age,name];
//    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL,NULL , NULL);
//    if (result == SQLITE_OK) {
//        NSLog(@"修改成功");
//    }else{
//        NSLog(@"修改失败");
//    }
}
-(void)deleteStudent:(NSString*)name{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM movielist WHERE name = '%@'",name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL,NULL , NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
-(void)dropTable{
    NSString *sql = [NSString stringWithFormat:@"DROP TABLE movielist"];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if(result == SQLITE_OK){
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}


@end
